﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

    public static class DBhelper
    {
        public static SqlConnection conn = new SqlConnection("Data Source=公瑾乔;Initial Catalog=HouseProperty;Integrated Security=True");
    
       /// <summary>
       /// 返回受影响的行数，一般执行insert、update、delete操作
       /// </summary>
       /// <param name="sql">要执行的sql语句</param>
       /// <returns>返回一个int类型，就是受影响的行数</returns>
        public static int NonQuery(string sql)
        {
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                int i = cmd.ExecuteNonQuery();         
                return i;//将受影响的行数返回
            }
            catch (Exception se)
            {
                throw se;
            }
            finally
            {
                conn.Close();
            }             
        }

        /// <summary>
        /// 返回一个临时表数据，一般执行显示表中多条数据操作  select
        /// </summary>
        /// <param name="sql">要执行的查询sql语句</param>
        /// <returns>返回一个DataTable</returns>
        public static DataTable GetTable(string sql)
        {
            try
            {
                conn.Open();
                SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
                DataTable dt = new DataTable();
                sda.Fill(dt);             
                return dt;
            }
            catch (Exception se)
            {
                throw se;
            }
            finally {
                conn.Close();
            }
            
        }

        /// <summary>
        /// 执行查询操作，返回一个结果集
        /// </summary>
        /// <param name="sql">要执行的sql语句</param>
        /// <returns>返回一个读取器</returns>
        public static SqlDataReader Reader(string sql)
        {
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql,conn);
                SqlDataReader sdr =  cmd.ExecuteReader(CommandBehavior.CloseConnection);//读取器和数据源此处不能被关闭
                return sdr;
            }
            catch (Exception se)
            {
                
                throw se;
            }   
        }
        /// <summary>
        /// 执行查询操作，返回一个单值，一般执行根据名称找编号或执行聚合函数
        /// </summary>
        /// <param name="sql">要执行的sql查询语句</param>
        /// <returns>返回一个object类型</returns>
        public static object Scalar(string sql)
        {
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                object obj = cmd.ExecuteScalar();
                return obj;
            }
            catch (Exception se)
            {
                throw se;
            }
            finally {
                conn.Close();
            }
           
        }
        
    }
